﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using University.Workshop.Model;

namespace University.Workshop.DataAccess
{
    public static class ProductDataAccess
    {
        public static DataSet LoadNBuyProduct()
        {
            string stringConnection = "select Max(IdBuyProduct) from Buy";
            using(DBConnection connection = new DBConnection())
            {
                if (connection.ConsultationSelect(stringConnection))
                {
                    return connection.ExtractData(stringConnection);
                }
                else 
                {
                    return null;
                }
            }
        }

        public static bool ProductRegister(Product product, ProductBuy buy)
        {
            if (product != null && buy != null)
            {
                string stringConnection;
                if (LoadNameProduct(product.NameProduct).Rows.Count == 0 )
                {
                    stringConnection = "insert into Product(IdProduct,GroupProduct,NameProduct,StockCurrent)values(" + product.IdProduct + ",'" + product.GroupProduct + "','" + product.NameProduct + "'," + product.StockCurrent + ")";
                }
                else
                {
                    int sum = product.StockCurrent + buy.StockOrder;
                    stringConnection = "Update Product set StockCurrent="+sum+"";
                }
                if (ExecuteConsult(stringConnection))
                {
                    string stringConnectionTwo = "insert into Buy(DateBuy,Supplier,StockOrder,UnitPrice,TotalPrice,ImageBuy,Description,Product_IdProduct,Manager_CiManager)values('" + buy.DateBuy + "','" + buy.Supplier + "'," + buy.StockOrder + "," + buy.UnitPrice + "," + buy.TotalPrice + ",'" + buy.ImageBuy + "','" + buy.Description + "',"+product.IdProduct+","+buy.CIManager+")";
                    return ExecuteConsult(stringConnectionTwo);
                }
                else
                {
                    return false;
                }
            }
            else 
            {
                return false;
            }
        }
        private static bool ExecuteConsult(string stringConnection)
        {
            using (DBConnection connection = new DBConnection())
            {
                if (connection.CommandExecuteConsult(stringConnection))
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        public static DataTable LoadDataGridBuy()
        {
            string stringConnection = "Select * from Buy";
            using (DBConnection connection = new DBConnection())
            {
                return connection.ConsultationSelectDataTable(stringConnection);
            }
        }

        public static DataTable LoadProduct(string product)
        {
            string stringConnection = "Select * from Product where GroupProduct ='"+product+"'";
            using (DBConnection connection = new DBConnection()) 
            {
                return connection.ConsultationSelectDataTable(stringConnection);
            }
        }
        public static DataTable LoadNameProduct(string product)
        {
            string stringConnection = "Select * from Product where NameProduct ='" + product + "'";
            using (DBConnection connection = new DBConnection())
            {
                return connection.ConsultationSelectDataTable(stringConnection);
            }
        }
        public static DataTable LoadDataGridViewSearchBuyProduct(string searchProduct)
        {
            string stringConnection = "Select * From Buy where IdBuyProduct like'%" + searchProduct + "%'";
            using (DBConnection connection = new DBConnection())
            {
                if (connection.ConsultationSelect(stringConnection))
                {
                    return connection.ConsultationSelectDataTable(stringConnection);
                }
                else
                {
                    stringConnection = "Select * From Buy where DateBuy like'%" + searchProduct + "%'";
                    if (connection.ConsultationSelect(stringConnection))
                    {
                        return connection.ConsultationSelectDataTable(stringConnection);
                    }
                    else
                    {
                        stringConnection = "Select * From Buy where Supplier like'%" + searchProduct + "%'";
                        if (connection.ConsultationSelect(stringConnection))
                        {
                            return connection.ConsultationSelectDataTable(stringConnection);
                        }
                        else
                        {
                            return null;
                        }

                    }
                }
            }
        }

        public static DataTable LoadDataGridProduct(string idProduct)
        {
            string stringConnection = "Select * from Product where IdProduct='" + idProduct + "'";
            using (DBConnection connection = new DBConnection())
            {
                return connection.ConsultationSelectDataTable(stringConnection);
            }
        }

        public static bool ProductModify(Product product, ProductBuy buy)
        {
            if (product != null && buy != null)
            {
                string stringConnection = "update Product set GroupProduct='" + product.GroupProduct + "',NameProduct='" + product.NameProduct + "',StockCurrent='" + product.StockCurrent + "'where IdProduct='" + product.IdProduct + "'";
                if (ExecuteConsult(stringConnection)) 
                {
                    string stringConnectionBuy = "update Buy set DateBuy='" + buy.DateBuy + "',Supplier='" + buy.Supplier + "',StockOrder=" + buy.StockOrder + ",UnitPrice="+buy.UnitPrice+", TotalPrice= "+buy.TotalPrice+",ImageBuy='"+buy.ImageBuy+"',Description='"+buy.Description+"'where IdBuyProduct=" + buy.IdBuyProduct + "";
                    return ExecuteConsult(stringConnectionBuy); 
                }
                else
                {
                    return false;
                }
            }
            else
            {
                return false;
            }
        }
    }
}
